# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt


import frappe
from frappe import _


def execute(filters=None):
	# get_data要传参filters
	# message = []
	# message.append("<br>The value of the Creation To Filter is : " + filters.get("creation_to"))
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	# return ["申请单号:Link/SRM Sample Request:150", "内部状态:80", "项目名称:150","物料名称:150", "规格型号:200", "供应商:150", "元件数量:50",
	# 		"采购员:60", "样品承认:50", "申请人:50", "产品线_事业部:120", "样机:50", "试产:50", "订单支持:50", "售后:50", "生产制造:50", "其他:50"
	# 		]
	columns = [
		{
			"label": _("申请单号"),
			"fieldtype": "Link",
			"fieldname": "name",
			"options": "SRM Sample Request",
			"width": 150,
		},
		{
			"label": _("需求日期"),
			"fieldtype": "Date",
			"fieldname": "requirement_date",
			"width": 100,
		},
		{
			"label": _("内部状态"),
			"fieldtype": "Data",
			"fieldname": "workflow_state",
			"width": 80,
		},
		{
			"label": _("项目名称"),
			"fieldtype": "Data",
			"fieldname": "project_name",
			"width": 150,
		},
		{
			"label": _("物料名称"),
			"fieldtype": "Data",
			"fieldname": "sample_category_purchase",
			"width": 100,
		},
		{
			"label": _("规格型号"),
			"fieldtype": "Data",
			"fieldname": "sample_model",
			"width": 280,
		},
		{
			"label": _("优先级"),
			"fieldtype": "Data",
			"fieldname": "priority",
			"width": 70,
		},
		{
			"label": _("任务类型"),
			"fieldtype": "Data",
			"fieldname": "type",
			"width": 80,
		},
		{
			"label": _("供应商"),
			"fieldtype": "Data",
			"fieldname": "curren_supplier",
			"width": 180,
		},
		{
			"label": _("元件数量"),
			"fieldtype": "Data",
			"fieldname": "element_qty_2",
			"width": 80,
		},
		{
			"label": _("采购员"),
			"fieldtype": "Data",
			"fieldname": "purchaser_name",
			"width": 70,
		},
		{
			"label": _("样品承认"),
			"fieldtype": "Data",
			"fieldname": "sample_state",
			"width": 80,
		},
		{
			"label": _("申请人"),
			"fieldtype": "Data",
			"fieldname": "applicant_name",
			"width": 70,
		},
		{
			"label": _("申请日期"),
			"fieldtype": "Date",
			"fieldname": "creation",
			"width": 120,
		},
		{
			"label": _("产品线_事业部"),
			"fieldtype": "Data",
			"fieldname": "department_and_line",
			"width": 120,
		},
		{
			"label": _("预计回样日期_采购"),
			"fieldtype": "Date",
			"fieldname": "answer_date_from_purchase",
			"width": 180,
		},
		{
			"label": _("实际回样日期"),
			"fieldtype": "Date",
			"fieldname": "real_receive_date",
			"width": 120,
		},
		{
			"label": _("实际回样数量"),
			"fieldtype": "Data",
			"fieldname": "real_receive_qty",
			"width": 120,
		},
		{
			"label": _("样机"),
			"fieldtype": "Data",
			"fieldname": "is_prototype",
			"width": 60,
		},
		{
			"label": _("试产"),
			"fieldtype": "Data",
			"fieldname": "is_trial_production",
			"width": 60,
		},
		{
			"label": _("订单支持"),
			"fieldtype": "Data",
			"fieldname": "is_order_support",
			"width": 80,
		},
		{
			"label": _("售后"),
			"fieldtype": "Data",
			"fieldname": "is_service",
			"width": 80,
		},
		{
			"label": _("生产制造"),
			"fieldtype": "Data",
			"fieldname": "is_manufacturing",
			"width": 80,
		},
		{
			"label": _("Note Of Delay"),
			"fieldtype": "Data",
			"fieldname": "note_of_delay",
			"width": 60,
		},
		{
			"label": _("其他"),
			"fieldtype": "Data",
			"fieldname": "is_other_note",
			"width": 60,
		},
	]	

	return columns


def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	creation_from, creation_to = filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	dt = frappe.db.sql(
		f"""select IFNULL(s1.name,'') as name, s1.requirement_date,
			case s1.workflow_state when 'Save' then '保存'
								when 'Hand In' then '提交'
								when 'Be Received' then '待收料'
								when 'Rejected' then '拒绝'
								when 'Finish' then '完成' 
								when 'Cancelled' then '取消'
								when 'Return From Purchase' then '采购打回'  end as workflow_state, 
			s1.project_name as project_name, IFNULL(s1.sample_category_purchase,'') as sample_category_purchase, 
			s1.sample_model as sample_model, IFNULL(s1.curren_supplier, '') as curren_supplier, s1.element_qty_2 as element_qty_2,
			IFNULL(s1.purchaser_name,'') as purchaser_name,
			s1.sample_state as sample_state, s1.applicant_name as applicant_name, s1.creation, 
			s1.department_and_line as department_and_line,
			case s1.is_prototype when 1 then '样机' else '' end as is_prototype,
			case s1.is_trial_production when 1 then '试产' else '' end as is_trial_production,
			case s1.is_order_support when 1 then '订单支持' else '' end as is_order_support,
			case s1.is_service when 1 then '售后' else '' end as is_service,
			case s1.is_manufacturing when 1 then '生产制造' else '' end as is_manufacturing,
			case s1.is_other_note when 1 then '其他' else '' end as is_other_note,
			s1.priority,
			s1.type,
			IFNULL(s1.answer_date_from_purchase, '') as answer_date_from_purchase,
			IFNULL(s1.real_receive_qty,'') as real_receive_qty,
			IFNULL(s1.real_receive_date, '') as real_receive_date,
			s1.note_of_delay
			from `tabSRM Sample Request` s1 
			WHERE s1.creation >= '{creation_from}' and s1.creation <= '{creation_to}' {conditions}
			ORDER BY s1.creation DESC;
		""", as_dict=True
	)
	return dt
